{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Figure size 800x600 with 0 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import helper_400\n",
    "\n",
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "helper_400.set_sns_style()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"data/California.csv\", usecols= [\n",
    "    \"STCNTY\",\n",
    "    \"COUNTY\",\n",
    "    \"FIPS\",\n",
    "    \"LOCATION\",\n",
    "    \"AREA_SQMI\",\n",
    "    \"E_TOTPOP\",\n",
    "    \"E_HU\",\n",
    "    \"E_HH\",\n",
    "    \"EP_POV150\",\n",
    "    \"EP_UNEMP\",\n",
    "    \"EP_HBURD\",\n",
    "    \"EP_NOHSDP\",\n",
    "    \"EP_UNINSUR\",\n",
    "    \"EP_AGE65\",\n",
    "    \"EP_AGE17\",\n",
    "    \"EP_DISABL\",\n",
    "    \"EP_SNGPNT\",\n",
    "    \"EP_LIMENG\",\n",
    "    \"EP_MINRTY\",\n",
    "    \"EP_NOVEH\",\n",
    "    # \"RPL_THEME1\",\n",
    "    # \"RPL_THEME2\",\n",
    "    # \"RPL_THEME3\",\n",
    "    # \"RPL_THEME4\",\n",
    "    \"RPL_THEMES\",\n",
    "], dtype={\"FIPS\":str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"FIPSI\"]=df[\"FIPS\"].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "exposure = helper_400.get_exposure_df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8673"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(exposure)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(exposure, left_on=\"FIPSI\", right_on=\"GEOID\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "avg = pd.DataFrame(np.empty((1, df.shape[1])), columns=df.columns)\n",
    "# For numerical columns, use mean\n",
    "for col in df.select_dtypes(include=[np.number]):\n",
    "    avg.at[0, col] = df[col].mean()\n",
    "\n",
    "# For non-numerical columns, use mode\n",
    "for col in df.select_dtypes(exclude=[np.number]):\n",
    "    avg.at[0, col] = \"Aggregated Average\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STCNTY</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>AREA_SQMI</th>\n",
       "      <th>E_TOTPOP</th>\n",
       "      <th>E_HU</th>\n",
       "      <th>E_HH</th>\n",
       "      <th>EP_POV150</th>\n",
       "      <th>EP_UNEMP</th>\n",
       "      <th>...</th>\n",
       "      <th>heatday</th>\n",
       "      <th>smoke_pm_non_zero</th>\n",
       "      <th>smoke_pm_gt_five</th>\n",
       "      <th>hw</th>\n",
       "      <th>hs</th>\n",
       "      <th>hws</th>\n",
       "      <th>ws</th>\n",
       "      <th>hs5</th>\n",
       "      <th>hws5</th>\n",
       "      <th>ws5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6054.952957</td>\n",
       "      <td>Aggregated Average</td>\n",
       "      <td>Aggregated Average</td>\n",
       "      <td>Aggregated Average</td>\n",
       "      <td>17.695392</td>\n",
       "      <td>4361.699758</td>\n",
       "      <td>1559.77528</td>\n",
       "      <td>1440.808601</td>\n",
       "      <td>21.168558</td>\n",
       "      <td>1.206757</td>\n",
       "      <td>...</td>\n",
       "      <td>291.36066</td>\n",
       "      <td>382.35893</td>\n",
       "      <td>134.244206</td>\n",
       "      <td>0.202122</td>\n",
       "      <td>56.476075</td>\n",
       "      <td>0.079903</td>\n",
       "      <td>0.648565</td>\n",
       "      <td>20.980975</td>\n",
       "      <td>0.042315</td>\n",
       "      <td>0.316615</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        STCNTY              COUNTY                FIPS            LOCATION  \\\n",
       "0  6054.952957  Aggregated Average  Aggregated Average  Aggregated Average   \n",
       "\n",
       "   AREA_SQMI     E_TOTPOP        E_HU         E_HH  EP_POV150  EP_UNEMP  ...  \\\n",
       "0  17.695392  4361.699758  1559.77528  1440.808601  21.168558  1.206757  ...   \n",
       "\n",
       "     heatday  smoke_pm_non_zero  smoke_pm_gt_five        hw         hs  \\\n",
       "0  291.36066          382.35893        134.244206  0.202122  56.476075   \n",
       "\n",
       "        hws        ws        hs5      hws5       ws5  \n",
       "0  0.079903  0.648565  20.980975  0.042315  0.316615  \n",
       "\n",
       "[1 rows x 34 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "avg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# locations with over three days exposure of the HWS5\n",
    "\n",
    "df = df[df[\"hws5\"] > 5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "16"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(avg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Append the mean row to the DataFrame\n",
    "df = pd.concat([df, avg], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STCNTY</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>AREA_SQMI</th>\n",
       "      <th>E_TOTPOP</th>\n",
       "      <th>E_HU</th>\n",
       "      <th>E_HH</th>\n",
       "      <th>EP_POV150</th>\n",
       "      <th>EP_UNEMP</th>\n",
       "      <th>...</th>\n",
       "      <th>heatday</th>\n",
       "      <th>smoke_pm_non_zero</th>\n",
       "      <th>smoke_pm_gt_five</th>\n",
       "      <th>hw</th>\n",
       "      <th>hs</th>\n",
       "      <th>hws</th>\n",
       "      <th>ws</th>\n",
       "      <th>hs5</th>\n",
       "      <th>hws5</th>\n",
       "      <th>ws5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6005.0</td>\n",
       "      <td>Amador</td>\n",
       "      <td>06005000402</td>\n",
       "      <td>Census Tract 4.02, Amador County, California</td>\n",
       "      <td>28.283515</td>\n",
       "      <td>4358.0</td>\n",
       "      <td>2044.0</td>\n",
       "      <td>1671.0</td>\n",
       "      <td>22.1</td>\n",
       "      <td>6.3</td>\n",
       "      <td>...</td>\n",
       "      <td>290.0</td>\n",
       "      <td>672.0</td>\n",
       "      <td>248.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>121.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6009.0</td>\n",
       "      <td>Calaveras</td>\n",
       "      <td>06009000302</td>\n",
       "      <td>Census Tract 3.02, Calaveras County, California</td>\n",
       "      <td>102.309048</td>\n",
       "      <td>3131.0</td>\n",
       "      <td>1250.0</td>\n",
       "      <td>948.0</td>\n",
       "      <td>31.3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>269.0</td>\n",
       "      <td>709.0</td>\n",
       "      <td>259.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6009.0</td>\n",
       "      <td>Calaveras</td>\n",
       "      <td>06009000505</td>\n",
       "      <td>Census Tract 5.05, Calaveras County, California</td>\n",
       "      <td>3.157461</td>\n",
       "      <td>993.0</td>\n",
       "      <td>1734.0</td>\n",
       "      <td>486.0</td>\n",
       "      <td>9.2</td>\n",
       "      <td>9.7</td>\n",
       "      <td>...</td>\n",
       "      <td>274.0</td>\n",
       "      <td>637.0</td>\n",
       "      <td>286.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>104.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6017.0</td>\n",
       "      <td>El Dorado</td>\n",
       "      <td>06017030601</td>\n",
       "      <td>Census Tract 306.01, El Dorado County, California</td>\n",
       "      <td>63.583718</td>\n",
       "      <td>5020.0</td>\n",
       "      <td>2236.0</td>\n",
       "      <td>2045.0</td>\n",
       "      <td>8.7</td>\n",
       "      <td>5.3</td>\n",
       "      <td>...</td>\n",
       "      <td>308.0</td>\n",
       "      <td>724.0</td>\n",
       "      <td>241.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>134.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>116.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6023.0</td>\n",
       "      <td>Humboldt</td>\n",
       "      <td>06023010901</td>\n",
       "      <td>Census Tract 109.01, Humboldt County, California</td>\n",
       "      <td>2.749053</td>\n",
       "      <td>4635.0</td>\n",
       "      <td>1962.0</td>\n",
       "      <td>1816.0</td>\n",
       "      <td>33.4</td>\n",
       "      <td>6.3</td>\n",
       "      <td>...</td>\n",
       "      <td>274.0</td>\n",
       "      <td>522.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   STCNTY     COUNTY         FIPS  \\\n",
       "0  6005.0     Amador  06005000402   \n",
       "1  6009.0  Calaveras  06009000302   \n",
       "2  6009.0  Calaveras  06009000505   \n",
       "3  6017.0  El Dorado  06017030601   \n",
       "4  6023.0   Humboldt  06023010901   \n",
       "\n",
       "                                            LOCATION   AREA_SQMI  E_TOTPOP  \\\n",
       "0       Census Tract 4.02, Amador County, California   28.283515    4358.0   \n",
       "1    Census Tract 3.02, Calaveras County, California  102.309048    3131.0   \n",
       "2    Census Tract 5.05, Calaveras County, California    3.157461     993.0   \n",
       "3  Census Tract 306.01, El Dorado County, California   63.583718    5020.0   \n",
       "4   Census Tract 109.01, Humboldt County, California    2.749053    4635.0   \n",
       "\n",
       "     E_HU    E_HH  EP_POV150  EP_UNEMP  ...  heatday  smoke_pm_non_zero  \\\n",
       "0  2044.0  1671.0       22.1       6.3  ...    290.0              672.0   \n",
       "1  1250.0   948.0       31.3       1.0  ...    269.0              709.0   \n",
       "2  1734.0   486.0        9.2       9.7  ...    274.0              637.0   \n",
       "3  2236.0  2045.0        8.7       5.3  ...    308.0              724.0   \n",
       "4  1962.0  1816.0       33.4       6.3  ...    274.0              522.0   \n",
       "\n",
       "   smoke_pm_gt_five    hw     hs   hws     ws   hs5  hws5   ws5  \n",
       "0             248.0  21.0  121.0  17.0   62.0  48.0  14.0  45.0  \n",
       "1             259.0  10.0  109.0   9.0   52.0  44.0   7.0  40.0  \n",
       "2             286.0  13.0  104.0  12.0   63.0  53.0   9.0  49.0  \n",
       "3             241.0  27.0  134.0  23.0  116.0  45.0  12.0  66.0  \n",
       "4             169.0  31.0   87.0  23.0  105.0  30.0  11.0  50.0  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['STCNTY', 'COUNTY', 'FIPS', 'LOCATION', 'AREA_SQMI', 'E_TOTPOP', 'E_HU',\n",
       "       'E_HH', 'EP_POV150', 'EP_UNEMP', 'EP_HBURD', 'EP_NOHSDP', 'EP_UNINSUR',\n",
       "       'EP_AGE65', 'EP_AGE17', 'EP_DISABL', 'EP_SNGPNT', 'EP_LIMENG',\n",
       "       'EP_MINRTY', 'EP_NOVEH', 'RPL_THEMES', 'FIPSI', 'GEOID', 'wfday',\n",
       "       'heatday', 'smoke_pm_non_zero', 'smoke_pm_gt_five', 'hw', 'hs', 'hws',\n",
       "       'ws', 'hs5', 'hws5', 'ws5'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(columns=['FIPSI', 'GEOID'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"most_exposed_locs_wfday_heat_smoke_305.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.12"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
